package com.yxsk.relay.job.admin.data.repository;

import com.yxsk.relay.job.admin.data.entity.JobTriggerLog;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Author 11376
 * @CreaTime 2019/6/22 11:05
 * @Description
 */
public interface JobTriggerLogRepository extends BaseRepository<JobTriggerLog> {

    @Query(nativeQuery = true, value = "SELECT qjd.JOB_NAME,qjd.EXECUTE_MODEL,qjd.TRIGGER_CLASS_NAME,jqa.NAME,tl.* FROM relay_job_trigger_log tl, relay_job_details qjd, relay_job_apps jqa \n" +
            "WHERE \n" +
            "tl.JOB_ID=qjd.ID AND \n" +
            "qjd.APP_ID=jqa.ID AND \n" +
            "IF(?1 != '', jqa.id=?1, 1=1) AND \n" +
            "IF(?2 != '', qjd.id=?2, 1=1) AND\n" +
            "IF(?3 != '', tl.TRIGGER_TIME>=?3, 1=1) AND\n" +
            "IF(?4 != '', tl.TRIGGER_TIME<=?4, 1=1)",
    countQuery = "SELECT COUNT(tl.ID) FROM relay_job_trigger_log tl, relay_job_details qjd, relay_job_apps jqa \n" +
            "WHERE \n" +
            "tl.JOB_ID=qjd.ID AND \n" +
            "qjd.APP_ID=jqa.ID AND \n" +
            "IF(?1 != '', jqa.id=?1, 1=1) AND \n" +
            "IF(?2 != '', qjd.id=?2, 1=1) AND\n" +
            "IF(?3 != '', tl.TRIGGER_TIME>=?3, 1=1) AND\n" +
            "IF(?4 != '', tl.TRIGGER_TIME<=?4, 1=1)")
    Page<Map> findPageByCondition(String appId, String jobId, Date startTime, Date endTime, PageRequest page);

    @Query(nativeQuery = true, value = "SELECT \n" +
            "  * \n" +
            "FROM\n" +
            "  relay_job_trigger_log t \n" +
            "WHERE IF(?1 != '', t.TRIGGER_TIME >=?1, 1=1) \n" +
            "  AND IF(?2 != '', t.TRIGGER_TIME <=?2, 1=1) \n" +
            "  AND IF(LENGTH(TRIM(?3)) > 0, t.JOB_ID IN (?3), 1=1) ORDER BY t.TRIGGER_TIME")
    List<JobTriggerLog> findData(Date startTime, Date endTime, List<String> jobIds);
}
